<%@page import="support.*, java.util.*, java.sql.*" %>

<HTML>
 <HEAD> <TITLE> Support Example</TITLE> </HEAD>
 <BODY>
  <% 
   	support s = new support();   	
   	
   	String path1 = config.getServletContext().getRealPath("countries.txt");
   	String path2 = config.getServletContext().getRealPath("universities.txt");
    String path3 = config.getServletContext().getRealPath("majors.txt");
    String path4 = config.getServletContext().getRealPath("specializations.txt");
	
    //getCountries returns a vector of the countries to be used for choosing citizenship
    Vector countries = s.getCountries(path1);    
    //getUniversities returns a vector of vectors where each vector is a tuple of <string, vector>
    //with the string being the name of the country/state and the vector being the list of universities there
    Vector universities = s.getUniversities(path2);
    //getMajors and getSpecializations return vectors
    Vector majors = s.getMajors(path3);
    Vector specializations = s.getSpecializations(path4);
    
    Connection conn = null;
    PreparedStatement pstmt = null;
    
    try {
        // Registering Postgresql JDBC driver with the DriverManager
        Class.forName("org.postgresql.Driver");

        // Open a connection to the database using DriverManager
        conn = DriverManager.getConnection(
            "jdbc:postgresql://localhost/cse135?" +
            "user=postgres&password=password");
        
        // Begin transaction
        conn.setAutoCommit(false);

        // insert countries
    	for(int i=0; i<countries.size(); i++) {
        	pstmt = conn.prepareStatement("INSERT INTO countries (country) VALUES (?)");
        	pstmt.setString(1, (String)countries.get(i));
        
        	int rowCount = pstmt.executeUpdate();
    	}
        
    	// insert majors
        for(int i=0; i<majors.size(); i++) {
        	pstmt = conn.prepareStatement("INSERT INTO disciplines (discipline) VALUES (?)");
        	pstmt.setString(1, (String)majors.get(i));
        
        	int rowCount = pstmt.executeUpdate();
        }
    	// add one more major for "other" option
    	pstmt = conn.prepareStatement("INSERT INTO disciplines (discipline) VALUES (?)");
        pstmt.setString(1, "Other");
        
        int rowOtherCount = pstmt.executeUpdate();
        	
    	// insert specializations
        for(int i=0; i<specializations.size(); i++) {
        	pstmt = conn.prepareStatement("INSERT INTO specializations (specialization) VALUES (?)");
        	pstmt.setString(1, (String)specializations.get(i));
        
        	int rowCount = pstmt.executeUpdate();
        }
    	
    	// insert university-locations
        for (int i=0; i<universities.size(); i++) {
            Vector tuple = (Vector)universities.get(i);
            String state = (String)tuple.get(0);
            
            pstmt = conn.prepareStatement("INSERT INTO university_locations (location) VALUES (?)");
        	pstmt.setString(1, state);
        
        	int rowCount = pstmt.executeUpdate();
        }
    	
     	// insert universities
        for (int i=0; i<universities.size(); i++) {
            Vector tuple = (Vector)universities.get(i);
            Vector u = (Vector)tuple.get(1);
            
            for(int j=0; j<u.size(); j++) {            
            	pstmt = conn.prepareStatement("INSERT INTO universities (university, location) VALUES (?, ?)");
        		pstmt.setString(1, (String)u.get(j));
        		pstmt.setInt(2, i+1);
        
        		int rowCount = pstmt.executeUpdate();
            }
        }
            
        // Commit transaction
        conn.commit();
        conn.setAutoCommit(true);

        // Close the Connection
        conn.close();
    } catch (SQLException e) {

        // Wrap the SQL exception in a runtime exception to propagate
        // it upwards
        throw new RuntimeException(e);
    }
    finally {
        // Release resources in a finally block in reverse-order of
        // their creation
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) { } // Ignore
            pstmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) { } // Ignore
            conn = null;
        }
    }
 %>
 </BODY>
</HTML>

